#include "stumysql.h"
#include <QCoreApplication>
#include <QtDebug>
stumySql *stumySql::ptrstumysql = nullptr;
stumySql::stumySql(QObject *parent)
    : QObject{parent}
{
    //init();
    // stuInfo s;
    // s.name="悠游";
    // for(int i=0;i<5;i++)
    // {
    //     addstu(s);
    // }
    //getStuCnt();
}

void stumySql::init()
{
    // if(QSqlDatabase::drivers().isEmpty())//是否存在可用的数据库驱动
    //     qDebug()<<"No database drivers found";
    db = QSqlDatabase::addDatabase("QODBC");
    db.setHostName("127.0.0.1");
    db.setPort(3306);
    db.setDatabaseName("Mysql"); //不是Database,记得别填错
    db.setUserName("root");
    db.setPassword("123456");
    //auto str=QCoreApplication::applicationDirPath();
    bool ok = db.open();
    if (ok) {
        QMessageBox::information(NULL, "infor", "link success");
    } else {
        QMessageBox::information(NULL, "infor", "link failed");
        qDebug() << "error open database because" << db.lastError().text();
    }
}

quint32 stumySql::getStuCnt()
{
    QSqlQuery sql(db);
    sql.exec("select Count(id) from student;");
    quint32 uiCnt = 0;
    while (sql.next()) {
        uiCnt = sql.value(0).toUInt();
    }
    return uiCnt;
}

QList<stuInfo> stumySql::getPageStu(quint32 page,
                                    quint32 uiCnt) //uiCnt是要开始查询的行数,page是要查几行
{
    QList<stuInfo> l;  //存储查询到的学生信息
    QSqlQuery sql(db); //与数据库关联
    QString strsql = QString("select * from student order by id limit %1 offset %2")
                         .arg(uiCnt)
                         .arg(page * uiCnt);
    sql.exec(strsql);
    stuInfo info;
    while (sql.next()) {
        info.id = sql.value(0).toUInt();
        info.name = sql.value(1).toString();
        info.age = sql.value(2).toUInt();
        info.grade = sql.value(3).toUInt();
        info.uiclass = sql.value(4).toUInt();
        info.studentid = sql.value(5).toUInt();
        info.phone = sql.value(6).toString();
        info.wechat = sql.value(7).toString();
        l.push_back(info);
    }
    return l;
}

bool stumySql::addstu(stuInfo info) //增加学生
{
    QSqlQuery sql(db);
    QString strsql = QString("INSERT INTO student VALUES(null,'%1',%2,%3,%4,%5,'%6','%7')")
                         .arg(info.name)
                         .arg(info.age)
                         .arg(info.grade)
                         .arg(info.uiclass)
                         .arg(info.studentid)
                         .arg(info.phone)
                         .arg(info.wechat);
    return sql.exec(strsql);
}

bool stumySql::delStu(int id) //删除学生
{
    QSqlQuery sql(db);

    return sql.exec(QString("delete from student where id=%1").arg(id));
}

bool stumySql::clearStuTable() //清空学生表
{
    QSqlQuery sql(db);
    //sql.exec("delete from student");
    return sql.exec("TRUNCATE TABLE student"); //清空表后保证自增ID从1开始
}

bool stumySql::UpdateStuInfo(stuInfo info) //修改学生信息
{
    QSqlQuery sql(db);
    QString strsql
        = QString("update student set name = "
                  "'%1',age=%2,grade=%3,class=%4,studentid=%5,phone='%6',wechat='%7' where id=%8")
              .arg(info.name)
              .arg(info.age)
              .arg(info.grade)
              .arg(info.uiclass)
              .arg(info.studentid)
              .arg(info.phone)
              .arg(info.wechat)
              .arg(info.id);
    return sql.exec(strsql);
}

quint32 stumySql::getUserCnt()
{
    QSqlQuery sql(db);
    sql.exec("SELECT COUNT(*) FROM user");
    quint32 uiCnt = 0;
    while (sql.next()) {
        uiCnt = sql.value(0).toUInt();
    }
    return uiCnt;
}

QList<UserInfo> stumySql::getPageUser(quint32 page, quint32 uiCnt)
{
    QList<UserInfo> l; //存储查询到的学生信息
    QSqlQuery sql(db); //与数据库关联
    QString strsql
        = QString("select * from user order by id limit %1 offset %2").arg(uiCnt).arg(page * uiCnt);
    sql.exec(strsql);
    UserInfo info;
    while (sql.next()) {
        info.username = sql.value(0).toString();
        info.password = sql.value(1).toString();
        info.aut = sql.value(2).toString();
        l.push_back(info);
    }
    return l;
}

QList<UserInfo> stumySql::getAllUser() //查询所有用户
{
    QList<UserInfo> l;
    QSqlQuery sql(db);
    sql.exec("select*from user");
    UserInfo info;
    while (sql.next()) {
        info.username = sql.value(0).toString();
        info.password = sql.value(1).toString();
        info.aut = sql.value(2).toString();
        l.push_back(info);
    }
    return l;
}

QString stumySql::Selectpassword(QString strUsername)
{
    QSqlQuery sql(db);
    sql.exec(QString("SELECT password FROM user WHERE username ='%1'").arg(strUsername));
    if (sql.next()) {
        return sql.value(0).toString(); // 返回查询结果中第一列的值（即密码）
    } else {
        return ""; // 如果查询结果为空，返回空字符串
    }
}

bool stumySql::isExist(QString strUser) //查询用户名是否存在
{
    QSqlQuery sql(db);
    sql.exec(QString("select *from user where username='%1'").arg(strUser));
    return sql
        .next(); //next()函数是QSqlQuery类的一个成员函数，用于在查询结果中移动到下一条记录,当调用next()函数时，如果当前查询结果中有下一条记录，则会移动到下一条记录并返回true；如果当前已经是最后一条记录或者查询结果为空，则返回false
    //QDebug<<sql.next();
}

bool stumySql::updataUser(UserInfo info)
{
    QSqlQuery sql(db);
    QString strsql = QString("update user set password='%1',aut='%2' where username='%3';")
                         .arg(info.password)
                         .arg(info.aut)
                         .arg(info.username);
    return sql.exec(strsql);
}

bool stumySql::AddUser(UserInfo info)
{
    QSqlQuery sql(db);
    QString strsql = QString("INSERT INTO user VALUES ('%1', '%2', '%3')")
                         .arg(info.username)
                         .arg(info.password)
                         .arg(info.aut);
    return sql.exec(strsql);
}

bool stumySql::delUser(QString strUsername)
{
    QSqlQuery sql(db);
    return sql.exec(QString("delete from user where username='%1'").arg(strUsername));
}
